﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class CatalogueDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.CatalogueTableAdapter tda;
        private SSISDataSetTableAdapters.CategoryTableAdapter categorytda;
        private SSISDataSetTableAdapters.CategoryAndCatalogueTableAdapter cctda;

        private int index;
        private List<Catalogue> catal_list;
        private List<Category> categ_list;        
        private SSISDataSet.CategoryDataTable category_td;
        private SSISDataSet.CatalogueDataTable catalogue_td;
        private SSISDataSet.CategoryAndCatalogueDataTable cc_td;

        //singleton
        private static bool instance_flag = false;
        private static CatalogueDBBroker instance;


        private CatalogueDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.CatalogueTableAdapter();
            categorytda = new SSISDataSetTableAdapters.CategoryTableAdapter();
            cctda = new SSISDataSetTableAdapters.CategoryAndCatalogueTableAdapter();

            //tda.Fill(ds.Catalogue);
            //categorytda.Fill(ds.Category);
            //cctda.Fill(ds.CategoryAndCatalogue);
        }

        public static CatalogueDBBroker getCatalogueDBBroker()
        {
            if (!instance_flag)
            {
                instance = new CatalogueDBBroker();
                instance_flag = true;
                
                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Catalogue> selectAllCatalogue()
        {
            tda.Fill(ds.Catalogue);
            catal_list = new List<Catalogue>();
            if (ds.Catalogue.Rows.Count > 0)
            {
                foreach (SSISDataSet.CatalogueRow row in ds.Catalogue.Rows)
                {
                    Catalogue catal = new Catalogue();
                    catal.ItemId = row.ItemId;
                    catal.CategoryId = row.CategoryId;
                    catal.Description = row.Description;
                    catal.BinId = row.BinId;
                    catal.ReOrderLevel = row.ReOrderLevel;
                    catal.ReOrderQty = row.ReOrderQty;
                    catal.UOM = row.UOM;
                    catal.TotalQty = row.TotalQty;
                    catal_list.Add(catal);
                }
                return catal_list;
            }
            else
            {
                return null;
            }
        }

        //return data table
        public DataTable selectAllCatalogue_DT()
        {
            return ds.Catalogue;
        }
             

        public int addCatalogue(Catalogue catelogue)
        {
            SSISDataSet.CatalogueRow newRow = ds.Catalogue.NewCatalogueRow();
            newRow.ItemId = catelogue.ItemId;
            newRow.CategoryId = catelogue.CategoryId;
            newRow.Description = catelogue.Description;
            newRow.BinId = catelogue.BinId;
            newRow.ReOrderLevel = catelogue.ReOrderLevel;
            newRow.ReOrderQty = catelogue.ReOrderQty;
            newRow.UOM = catelogue.UOM;
            newRow.TotalQty = catelogue.TotalQty;
            ds.Catalogue.Rows.Add(newRow);
            return tda.Update(ds.Catalogue);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.CatalogueRow row = ds.Catalogue.FindByItemId(Id);
        //    return ds.Catalogue.Rows.IndexOf(row);
        //}

        private SSISDataSet.CatalogueRow getRowById(String id)
        {
            tda.Fill(ds.Catalogue);
            SSISDataSet.CatalogueRow r = ds.Catalogue.FindByItemId(id);
            return r;
        }

        public int updateCatalogue(Catalogue catalogue, String Id)
        {
            SSISDataSet.CatalogueRow row = getRowById(Id);
            if (row != null)
            {
                if (catalogue != null && !(catalogue.TotalQty == 0))
                {
                    row.TotalQty = (row.TotalQty + catalogue.TotalQty);
                }
            }

            return tda.Update(ds.Catalogue);
        }

        public int updateCatalogueTotalQty(Catalogue catalogue, String Id)
        {
            SSISDataSet.CatalogueRow row = getRowById(Id);
            if (row != null)
            {
                if (catalogue != null && !(catalogue.TotalQty == 0))
                {
                    row.TotalQty = catalogue.TotalQty;
                }
            }

            return tda.Update(ds.Catalogue);
        }

        public int deleteCatalogue(String Id)
        {
            return tda.DeleteByItemId(Id);

        }

        public Catalogue selectCatalogue(String Id)
        {
            tda.Fill(ds.Catalogue);
            SSISDataSet.CatalogueRow r = getRowById(Id);
            if (r != null)
            {
                Catalogue catal = new Catalogue();
                catal.ItemId = r.ItemId;
                catal.CategoryId = r.CategoryId;
                catal.Description = r.Description;
                catal.BinId = r.BinId;
                catal.ReOrderLevel = r.ReOrderLevel;
                catal.ReOrderQty = r.ReOrderQty;
                catal.UOM = r.UOM;
                catal.TotalQty = r.TotalQty;
                return catal;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> selectCatalogueByCategoryId(int Id)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByCategoryId(Id);

            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();

                foreach (SSISDataSet.CategoryAndCatalogueRow row in cc_td.Rows)
                {
                    Catalogue catal = new Catalogue();
                    catal.ItemId = row.ItemId;
                    catal.CategoryId = row.CategoryId;
                    catal.Description = row.Description;
                    catal.CategoryName = row.CategoryName;
                    catal.BinId = row.BinId;
                    catal.ReOrderLevel = row.ReOrderLevel;
                    catal.ReOrderQty = row.ReOrderQty;
                    catal.TotalQty = row.TotalQty;
                    catal.UOM = row.UOM;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        ///////////////////////////TT//////////////////////////////////////

        public List<Catalogue> getCatalogueByBinId(String binId)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByBinId(binId);

            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();

                foreach (SSISDataSet.CategoryAndCatalogueRow row in cc_td.Rows)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = row.BinId;
                    catal.CategoryId = row.CategoryId;
                    catal.Description = row.Description;
                    catal.ItemId = row.ItemId;
                    catal.ReOrderLevel = row.ReOrderLevel;
                    catal.ReOrderQty = row.ReOrderQty;
                    catal.TotalQty = row.TotalQty;
                    catal.UOM = row.UOM;
                    catal.CategoryName = row.CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByDesc(String desc)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByDesc("%" + desc + "%");

            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < cc_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = cc_td[i].BinId;
                    catal.CategoryId = cc_td[i].CategoryId;
                    catal.Description = cc_td[i].Description;
                    catal.ItemId = cc_td[i].ItemId;
                    catal.ReOrderLevel = cc_td[i].ReOrderLevel;
                    catal.ReOrderQty = cc_td[i].ReOrderQty;
                    catal.TotalQty = cc_td[i].TotalQty;
                    catal.UOM = cc_td[i].UOM;
                    catal.CategoryName = cc_td[i].CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByDescAndCategory(String desc , int categoryId)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByDescAndCategory("%" + desc + "%", categoryId);
            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < cc_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = cc_td[i].BinId;
                    catal.CategoryId = cc_td[i].CategoryId;
                    catal.Description = cc_td[i].Description;
                    catal.ItemId = cc_td[i].ItemId;
                    catal.ReOrderLevel = cc_td[i].ReOrderLevel;
                    catal.ReOrderQty = cc_td[i].ReOrderQty;
                    catal.TotalQty = cc_td[i].TotalQty;
                    catal.UOM = cc_td[i].UOM;
                    catal.CategoryName = cc_td[i].CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }


        public List<Catalogue> getCatalogueByBinAndCategory(String bin, int categoryId)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByBinAndCategory(bin, categoryId);
            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < cc_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = cc_td[i].BinId;
                    catal.CategoryId = cc_td[i].CategoryId;
                    catal.Description = cc_td[i].Description;
                    catal.ItemId = cc_td[i].ItemId;
                    catal.ReOrderLevel = cc_td[i].ReOrderLevel;
                    catal.ReOrderQty = cc_td[i].ReOrderQty;
                    catal.TotalQty = cc_td[i].TotalQty;
                    catal.UOM = cc_td[i].UOM;
                    catal.CategoryName = cc_td[i].CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByBinAndDesc(String bin, String desc)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByBinAndDesc(bin, "%" + desc + "%"); ;
            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < cc_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = cc_td[i].BinId;
                    catal.CategoryId = cc_td[i].CategoryId;
                    catal.Description = cc_td[i].Description;
                    catal.ItemId = cc_td[i].ItemId;
                    catal.ReOrderLevel = cc_td[i].ReOrderLevel;
                    catal.ReOrderQty = cc_td[i].ReOrderQty;
                    catal.TotalQty = cc_td[i].TotalQty;
                    catal.UOM = cc_td[i].UOM;
                    catal.CategoryName = cc_td[i].CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueByBinAndCategoryAndDesc(String bin,int categoryId, String desc)
        {
            cctda.Fill(ds.CategoryAndCatalogue);
            cc_td = cctda.GetDataByBinAndCategoryAndDesc(bin, categoryId, "%" + desc + "%"); ;
            if (cc_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < cc_td.Rows.Count; i++)
                {
                    Catalogue catal = new Catalogue();
                    catal.BinId = cc_td[i].BinId;
                    catal.CategoryId = cc_td[i].CategoryId;
                    catal.Description = cc_td[i].Description;
                    catal.ItemId = cc_td[i].ItemId;
                    catal.ReOrderLevel = cc_td[i].ReOrderLevel;
                    catal.ReOrderQty = cc_td[i].ReOrderQty;
                    catal.TotalQty = cc_td[i].TotalQty;
                    catal.UOM = cc_td[i].UOM;
                    catal.CategoryName = cc_td[i].CategoryName;
                    catal_list.Add(catal);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }



        public List<Catalogue> getCatalogueForClerk()
        {
            int i = cctda.Fill(ds.CategoryAndCatalogue);
            if (i > 0)
            {
                catal_list = new List<Catalogue>();
                for (int j = 0; j < ds.CategoryAndCatalogue.Rows.Count; j++)
                {
                    Catalogue c = new Catalogue();
                    c.ItemId = ds.CategoryAndCatalogue[j].ItemId;
                    c.CategoryId = ds.CategoryAndCatalogue[j].CategoryId;
                    c.Description = ds.CategoryAndCatalogue[j].Description;
                    c.BinId = ds.CategoryAndCatalogue[j].BinId;
                    c.ReOrderLevel = ds.CategoryAndCatalogue[j].ReOrderLevel;
                    c.ReOrderQty = ds.CategoryAndCatalogue[j].ReOrderQty;
                    c.UOM = ds.CategoryAndCatalogue[j].UOM;
                    c.TotalQty = ds.CategoryAndCatalogue[j].TotalQty;
                    c.CategoryName = ds.CategoryAndCatalogue[j].CategoryName;
                    catal_list.Add(c);
                }

                return catal_list;
            }
            else
            {
                return null;
            }
        }

        public List<Catalogue> getCatalogueBelowReOrderLevel()
        {
            tda.Fill(ds.Catalogue);
            catalogue_td = tda.GetDataByBelowReOrderLevel();
            if (catalogue_td.Rows.Count > 0)
            {
                catal_list = new List<Catalogue>();
                for (int i = 0; i < catalogue_td.Rows.Count; i++)
                {
                    Catalogue c = new Catalogue();
                    c.ItemId = catalogue_td[i].ItemId;
                    c.CategoryId = catalogue_td[i].CategoryId;
                    c.Description = catalogue_td[i].Description;
                    c.BinId = catalogue_td[i].BinId;
                    c.ReOrderLevel = catalogue_td[i].ReOrderLevel;
                    c.ReOrderQty = catalogue_td[i].ReOrderQty;
                    c.UOM = catalogue_td[i].UOM;
                    c.TotalQty = catalogue_td[i].TotalQty;

                    catal_list.Add(c);
                }
                return catal_list;
            }
            else
            {
                return null;
            }
        }


        ////////////////////////////// TT/////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////

        //Category
        public List<Category> selectAllCategory()
        {
            int j = categorytda.Fill(ds.Category);
            categ_list = new List<Category>();
            if (j > 0)
            {
                for (int i = 0; i < ds.Category.Rows.Count; i++)
                {
                    Category categ = new Category();
                    categ.CategoryId = ds.Category[i].CategoryId;
                    categ.CategoryName = ds.Category[i].CategoryName;

                    //Catalogue
                    catal_list = selectCatalogueByCategoryId(ds.Category[i].CategoryId);
                    categ.Catalogue_list = catal_list;
                    categ_list.Add(categ);
                }
                return categ_list;
            }
            else
            {
                return null;
            }
        }

        public int addCategory(Category category)
        {
            SSISDataSet.CategoryRow newRow = ds.Category.NewCategoryRow();
            newRow.CategoryId = category.CategoryId;
            newRow.CategoryName = category.CategoryName;

            ds.Category.Rows.Add(newRow);
            return categorytda.Update(ds.Category);
        }

        //private int getIndex(int Id)
        //{
        //    categorytda.Fill(ds.Category);
        //    SSISDataSet.CategoryRow row = ds.Category.FindByCategoryId(Id);
        //    return ds.Category.Rows.IndexOf(row);
        //}

        private SSISDataSet.CategoryRow getRowById(int id)
        {
            categorytda.Fill(ds.Category);
            return ds.Category.FindByCategoryId(id);
        }

        public int updateCategory(Category category, int Id)
        {
            SSISDataSet.CategoryRow row = getRowById(Id);
            if (row != null)
            {
                if (category.CategoryName != null && !(category.CategoryName.Equals(" ")))
                {
                    row.CategoryName = category.CategoryName;
                }
            }

            return categorytda.Update(ds.Category);
        }

        public int deleteCategory(int Id)
        {
            return categorytda.DeleteByCategoryId(Id);
        }

        public Category selectCategory(int Id)
        {
            categorytda.Fill(ds.Category);
            SSISDataSet.CategoryRow r = getRowById(Id);
            if (r != null)
            {
                Category categ = new Category();
                categ.CategoryId = r.CategoryId;
                categ.CategoryName = r.CategoryName;

                //Catalogue
                catal_list = selectCatalogueByCategoryId(r.CategoryId);
                categ.Catalogue_list = catal_list;
                return categ;
            }
            else
            {
                return null;
            }
        }
    }
}
